source("../Rprofile.R", echo = FALSE) #load libraries
##
## Attaching package: 'gplots'
##
## The following object is masked from 'package:stats':
##
## lowess
##
##
## Attaching package: 'dplyr'
##
## The following object is masked from 'package:stats':
##
## filter
##
## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union
##
## Loading required package: bitops
##
## Attaching package: 'jsonlite'
##
## The following object is masked from 'package:utils':
##
## View
SQL Crosstabs
source("../01 SQL Crosstabs/Crosstab.R", echo = TRUE) #load crosstabs
##
## > dfrank <- data.frame(fromJSON(getURL(URLencode(gsub("\n",
## + " ", "129.152.144.84:5001/rest/native/?query=\n\"select TYPE, ZIP, PRICE, RANK() OV .... [TRUNCATED]
##
## > dfmax <- data.frame(fromJSON(getURL(URLencode(gsub("\n",
## + " ", "129.152.144.84:5001/rest/native/?query=\"SELECT TYPE, ZIP, PRICE, \nMAX(PRICE) .... [TRUNCATED]
##
## > dfnth <- data.frame(fromJSON(getURL(URLencode(gsub("\n",
## + " ", "129.152.144.84:5001/rest/native/?query=\"SELECT * FROM (SELECT TYPE, ZIP, PRIC .... [TRUNCATED]
##
## > dfcume <- data.frame(fromJSON(getURL(URLencode(gsub("\n",
## + " ", "129.152.144.84:5001/rest/native/?query=\"SELECT TYPE, ZIP, PRICE, \n(100.0 * .... [TRUNCATED]
tbl_df(dfrank)
## Source: local data frame [985 x 4]
##
## TYPE ZIP PRICE RANK
## 1 Condo 95603 260000 1
## 2 Condo 95608 250134 1
## 3 Condo 95608 182000 2
## 4 Condo 95608 139500 3
## 5 Condo 95621 116250 1
## 6 Condo 95621 69000 2
## 7 Condo 95624 71000 1
## 8 Condo 95628 142500 1
## 9 Condo 95630 240000 1
## 10 Condo 95648 188000 1
## .. ... ... ... ...
tbl_df(dfmax)
## Source: local data frame [985 x 5]
##
## TYPE ZIP PRICE MAX_PRICE CALCULATION
## 1 Condo 95603 260000 260000 0
## 2 Condo 95608 139500 250134 110634
## 3 Condo 95608 182000 250134 68134
## 4 Condo 95608 250134 250134 0
## 5 Condo 95621 69000 116250 47250
## 6 Condo 95621 116250 116250 0
## 7 Condo 95624 71000 71000 0
## 8 Condo 95628 142500 142500 0
## 9 Condo 95630 240000 240000 0
## 10 Condo 95648 138000 188000 50000
## .. ... ... ... ... ...
tbl_df(dfnth)
## Source: local data frame [66 x 4]
##
## TYPE ZIP PRICE NTH
## 1 Condo 95608 139500 3
## 2 Condo 95678 212500 3
## 3 Condo 95758 133000 3
## 4 Condo 95825 107666 3
## 5 Condo 95826 90000 3
## 6 Condo 95834 120000 3
## 7 Condo 95835 152000 3
## 8 Condo 95841 77000 3
## 9 Condo 95842 82732 3
## 10 Multi-Family 95828 159900 3
## .. ... ... ... ...
tbl_df(dfcume)
## Source: local data frame [985 x 4]
##
## TYPE ZIP PRICE CALCULATION
## 1 Condo 95603 260000 100.00000
## 2 Condo 95608 139500 55.77011
## 3 Condo 95608 182000 72.76100
## 4 Condo 95608 250134 100.00000
## 5 Condo 95621 69000 59.35484
## 6 Condo 95621 116250 100.00000
## 7 Condo 95624 71000 100.00000
## 8 Condo 95628 142500 100.00000
## 9 Condo 95630 240000 100.00000
## 10 Condo 95648 138000 73.40426
## .. ... ... ... ...
Tableau Crosstabs
Rank Crosstab shows the ranking of sum of price for each price broken down by type of residence and zipcode
MaxDifference Crosstab shows the max price and the difference between max price and each price broken down by type of residence and zipcode
Nth Crosstab shows the nth value of price for each zipcode broken down by type of residence and zipcode
Cume_dist Crosstab shows the percentage of the each price over max price broken down by type of residence and zipcode
